package com.laozhao.test;

import java.sql.*; 

/** 
* Created by IntelliJ IDEA. 
* File: DBUtils.java 
* User: leizhimin 
* Date: 2008-3-5 15:05:00 
*/ 
public class DBUtils { 
//    private static Log log = LogFactory.getLog(DBUtils.class); 
    private static final String tb_sql = "\n" + 
            " SELECT C.COLTYPE, C.NAME, C.REMARKS\n" + 
            "  FROM SYSIBM.SYSCOLUMNS C, SYSIBM.SYSTABLES T\n" + 
            " WHERE C.TBNAME = T.NAME\n" + 
            "   AND C.TBCREATOR = T.CREATOR\n" + 
            "   AND T.CREATOR = ?\n" + 
            "   AND T.NAME = ?"; 

    /** 
     * 获取数据库连接 
     * 
     * @return 数据连接 
     */ 
    public static Connection makeConnection() { 
        Connection conn = null; 
//        String url = PropertyUtils.getUrl(); 
//        String username = PropertyUtils.getUsername(); 
//        String password = PropertyUtils.getPassword(); 
//        String diverClassName = PropertyUtils.getDriverClassName(); 
//        try { 
//            Class.forName(diverClassName); 
//            conn = DriverManager.getConnection(url, username, password); 
//        } catch (ClassNotFoundException e) { 
//            e.printStackTrace(); 
//        } catch (SQLException e) { 
//            e.printStackTrace(); 
//        } 
        return conn; 
    } 

    public static void main(String args[]) { 
//        testDBConnection(); 
        getTableProperty("ZFZVF", "JG_NJQK"); 
    } 

    /** 
     * 数据连接测试类，并将测试结果打印到控制台。 
     * 
     * @return 返回查询结果字符串 
     */ 
    public static String testDBConnection() { 
    	
        Connection conn = makeConnection(); 
        StringBuffer sb = new StringBuffer(); 
        try { 
            Statement stmt = conn.createStatement(); 
            ResultSet rs = stmt.executeQuery("SELECT NAME,CTIME FROM SYSIBM.SYSTABLES WHERE NAME NOT LIKE 'DM_%' AND NAME NOT LIKE 'SYS%'"); 
            while (rs.next()) { 
                sb.append(rs.getString(1) + "\t\t\t\t" + rs.getString(2) + "\n"); 
            } 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        System.out.println(sb.toString()); 
        return sb.toString(); 
    } 

    /** 
     * 获取表到Java Bean的成员列表的Java代码 
     * @param schema 模式 
     * @param tbName 表名 
     * @return Java Bean的成员列表的Java代码 
     */ 
    public static String getTableProperty(String schema, String tbName) { 
        Connection conn = makeConnection(); 
        StringBuffer sb = new StringBuffer(); 
        sb.append(schema).append(".").append(tbName).append("\n"); 
        try { 
            PreparedStatement pstmt = conn.prepareStatement(tb_sql); 
            pstmt.setString(1, schema); 
            pstmt.setString(2, tbName); 
            ResultSet rs = pstmt.executeQuery(); 
            while (rs.next()) { 
                String type = typeProcessor(rs.getString(1).trim()); 
                String name = rs.getString(2).toLowerCase(); 
                String remark; 
                if (rs.getString(3) == null) { 
                    remark = ""; 
                } else { 
                    remark = rs.getString(3); 
                } 
                sb.append("\tprivate\t" + type + "\t" + name + ";\t\t\t//" + remark + "\n"); 
            } 
            rs.close(); 
            pstmt.close(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
        System.out.println(sb.toString()); 
        return sb.toString(); 
    } 

    /** 
     * SQL数据类型到Java数据类型的转换 
     * @param sqlType SQL数据类型 
     * @return Java数据类型 
     */ 
    public static String typeProcessor(String sqlType) { 
        String newType; 
        if (sqlType.equalsIgnoreCase("varchar")) newType = "String"; 
        else if (sqlType.equalsIgnoreCase("char")) newType = "String"; 
        else if (sqlType.equalsIgnoreCase("bigint")) newType = "Long"; 
        else if (sqlType.equalsIgnoreCase("smallint")) newType = "int"; 
        else if (sqlType.equalsIgnoreCase("integer")) newType = "int"; 
        else if (sqlType.equalsIgnoreCase("decimal")) newType = "double"; 
        else if (sqlType.equalsIgnoreCase("timestmp")) newType = "Date"; 
        else { 
            newType = sqlType; 
        } 
        return newType; 
    } 
}